﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Business;
using Model;
using System.Web.Security;
using MB.Util;
using System.Data.OleDb;
using System.Data;
using System.Transactions;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace MvcAppWeb.Controllers
{
    public class UserCellController : BaseDataController
    {
        //
        // GET: /UserCell/

        public ActionResult UserCell()//库位商品关联
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }
        #region 用户库位关联

        public JsonResult Query(string SUSERNAME, string SCELLNO)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            string where = " where 1=1 ";
            if (!string.IsNullOrEmpty(SUSERNAME))
            {
                where += " and a.USERNAME like'%" + SUSERNAME + "%'";
            }
            if (!string.IsNullOrEmpty(SCELLNO))
            {
                where += " and lu.CELLNO like'%" + SCELLNO + "%'";
            }
            BUserCell nb = new BUserCell();
            var result = nb.GetUserCellList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string Save(string USERCELLID)
        {
            string sjson = string.Empty;
            try
            {
                UserCell _UserCell = new UserCell();
                AppUserList model = (AppUserList)Session["UserInfo"];
                string UserID = Request["UserCode"];
                string CellNo = Request["CellNo"];
                string Remark = Request["txtRemark"];
                sjson = CheckUserCell(USERCELLID, UserID, string.Empty, CellNo, Remark, out _UserCell);
                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BUserCell nb = new BUserCell();
                if (string.IsNullOrEmpty(USERCELLID))
                {
                    nb.AddUserCell(_UserCell);
                }
                else
                {
                    _UserCell.USERCELLID = int.Parse(USERCELLID);
                    nb.EditUserCellByID(_UserCell);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string Delete(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BUserCell nb = new BUserCell();
                foreach (var id in ids.Split(','))
                {
                    nb.DeleteUserCell(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string Import(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope())
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 3)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            UserCell _UserCell = new UserCell();
                            string UserCode = lstModelStr[0].ToString();
                            string CellNo = lstModelStr[1].ToString();
                            string Remark = lstModelStr[2].ToString();
                            sjson = CheckUserCell(string.Empty, string.Empty, UserCode, CellNo, Remark, out _UserCell);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BUserCell nb = new BUserCell();
                            nb.AddUserCell(_UserCell);
                        }
                    }
                    else//上传的是EXCEL
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            UserCell _UserCell = new UserCell();
                            string UserCode = table.Rows[i][0].ToString();
                            string CellNo = table.Rows[i][2].ToString();
                            string Remark = table.Rows[i][7].ToString();
                            sjson = CheckUserCell(string.Empty, string.Empty, UserCode, CellNo, Remark, out _UserCell);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BUserCell nb = new BUserCell();
                            nb.AddUserCell(_UserCell);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        public FileResult ExportExcel(int page, int rows)
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            int total = 0;
            string USERNAME = Request["txtSUSERNAME"];
            string CELLNO = Request["txtSCELLNO"];
            string where = " where 1=1 ";
            if (!string.IsNullOrEmpty(USERNAME))
            {
                where += " and a.USERNAME like'%" + USERNAME + "%'";
            }
            if (!string.IsNullOrEmpty(CELLNO))
            {
                where += " and lu.CELLNO like'%" + CELLNO + "%'";
            }
            BUserCell nb = new BUserCell();
            List<UserCell> list = nb.GetUserCellList(page, int.MaxValue, out total, where);

            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "用户登录名", "用户名称", "库位", "创建人", "创建时间", "修改人", "修改时间", "备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].USERCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].USERNAME);
                rowtemp.CreateCell(2).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(3).SetCellValue(list[i].CREATER);
                rowtemp.CreateCell(4).SetCellValue(list[i].CREATEDATE.ToString());
                rowtemp.CreateCell(5).SetCellValue(list[i].EDITOR);
                rowtemp.CreateCell(6).SetCellValue(list[i].EDITDATE.ToString());
                rowtemp.CreateCell(7).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("UserCell"));
        }

        /// <summary>
        /// 校验并赋值得到用户库位Model
        /// </summary>
        /// <param name="USERCELLID"></param>
        /// <param name="UserCode"></param>
        /// <param name="CellNo"></param>
        /// <param name="Remark"></param>
        /// <param name="_UserCell"></param>
        /// <returns></returns>
        private string CheckUserCell(string USERCELLID, string UserID, string UserCode, string CellNo, string Remark, out UserCell _UserCell)
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];
            _UserCell = new UserCell();
            if (!string.IsNullOrEmpty(UserCode))//UserCode不为空则需要校验
            {
                AppUserList _AppUserList = new NewCommon().GetUserByCode(UserCode);
                if (_AppUserList == null)
                {
                    sjson = "{success:false,msg:'该用户不存在'}";
                    return sjson;
                }
                _UserCell.USERID = _AppUserList.USERID;
            }
            if (!string.IsNullOrEmpty(UserID))//UserID不为空则直接赋值
                _UserCell.USERID = int.Parse(UserID);
            _UserCell.CELLNO = CellNo;
            _UserCell.REMARK = Remark;
            _UserCell.CREATER = model.USERNAME;
            _UserCell.CREATEDATE = DateTime.Now;
            _UserCell.EDITOR = model.USERNAME;
            _UserCell.EDITDATE = DateTime.Now;
            BUserCell nb = new BUserCell();
            UserCell _UserCellCheck = nb.CheckUserCellPrimary(USERCELLID, _UserCell.USERID, _UserCell.CELLNO);
            if (_UserCellCheck != null)
            {
                sjson = "{success:false,msg:'该用户和该库位关联已存在！'}";
            }
            return sjson;
        }
        #endregion

    }
}
